package com.FH.GET_IPInfo;

import com.FH.organization.operator_log.OperatorLogPullDTO;
import com.FH.organization.operator_log.OperatorLogResource;
import com.FH.organization.people.KamMemberResource;
import com.FH.organization.people.KamMemberShowDTO;
import com.FH.util.*;
import com.itextpdf.text.DocumentException;
import org.codehaus.jettison.json.JSONException;
import org.codehaus.jettison.json.JSONObject;

import javax.ejb.Stateless;
import javax.inject.Inject;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * Created by Administrator on 2016/10/17.
 */
@Stateless
public class SignInfoDAOImpl extends AccessDB implements signInfoDAO {
    @Inject
    KamMemberResource kamMemberResource;
    @Inject
    OperatorLogResource operatorLogResource;
    @Override
    public ResultDTO getSignInfo(String search) {
        ResultDTO resultDTO = new ResultDTO();

        try {
            JSONObject json = new JSONObject(search);
            String existName = "0";

            String sql_w = "";//查询条件sql组合
            String organization_id="";
            if (json.has("organization_id")) {
                organization_id =json.getString("organization_id");
            }
            if (json.has("sign_time")) {
                sql_w += " AND sign_time = '" + json.getString("sign_time") + "'";
            }

            ArrayList<KamMemberShowDTO>  men=(ArrayList<KamMemberShowDTO>) kamMemberResource.getUserListInfo("{organization_id:"+organization_id+"}","1","1").getValues();

            for (int i = 0; i < men.size(); i++){
                KamMemberShowDTO man= men.get(i);
                String member_id=man.getMember_id();
                String sql = " select count(1) FROM sign_info  where sign_type = '1' AND uerId = '" +member_id + "'" + sql_w;
                existName = getReportingToOne(sql);
                if(!existName.equals("0")){
                    man.setSign_state("1");
                }else{
                    man.setSign_state("0");
                }

                String sql_out = " select count(1) FROM sign_info  where sign_type = '2' AND uerId = '" +member_id + "'" + sql_w;
                existName = getReportingToOne(sql_out);
                if(!existName.equals("0")){
                    man.setSign_out_state("1");
                }else{
                    man.setSign_out_state("0");
                }
            }


//            List<String[]> seresult = new ArrayList<String[]>();
//
//            List<SignInfoShowDTO> result = new ArrayList<SignInfoShowDTO>();
//            String sql = " select signId,uerId,sign_time,IP,create_time FROM sign_info  where 1=1 " + sql_w;
//            //     String sql ="select * from finance_budget" ;
//            seresult = this.getReportingTo(sql);
//
//            //构建返回对象
//            for (int i = 0; i < seresult.size(); i++) {
//
//                SignInfoShowDTO fbs = new SignInfoShowDTO();
//
////                    fbs.setAttachment_id(seresult.get(i)[1]);
////
////                    fbs.setAttachment_addr(image_pathSerAddr+seresult.get(i)[2]);
////                    fbs.setHead_portrait_time(seresult.get(i)[3]);
////                    fbs.setHead_portrait_type(seresult.get(i)[4]);
////                    fbs.setFile_type(seresult.get(i)[5]);
////                    fbs.setFile_name(seresult.get(i)[6]);
//
//
//                result.add(fbs);
//            }

            if (men.size() != 0) {
                resultDTO.setStatus("90000");
                resultDTO.setDescritpion(warnSrc.get_success);
            } else {
                resultDTO.setStatus("90001");
                resultDTO.setDescritpion(warnSrc.get_fail);
            }
            resultDTO.setValues(men);

        } catch (JSONException e) {
            e.printStackTrace();

        }
        return resultDTO;
    }

    @Override
    public ResultDTO addSignInfo(signInfoPullDTO signInfoPullDTO) {
        ResultDTO resultDTO = new ResultDTO();//构建返回对象

        SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");//设置日期格式
        SimpleDateFormat df1 = new SimpleDateFormat("HH:mm:ss");//设置日期格式
        SimpleDateFormat df2 = new SimpleDateFormat("yyyy-MM-dd");//设置日期格式
        String time2 = "";
        Date time1 = new Date();
        time2 = df2.format(time1);
        String time = df1.format(new Date());
        String uuid = UUID.randomUUID().toString().trim().replaceAll("-", "");


        String existName = "";
        String uerId = signInfoPullDTO.getUerId();
        if (uerId != null && !uerId.equals("") && time2 != null && !time2.equals("")) {
            if(signInfoPullDTO.getSign_type().equals("1")){
                String sql = "select count(1) from sign_info where " +
                        "(uerId='" + signInfoPullDTO.getUerId() + "' or IP='" + signInfoPullDTO.getIP() + "') " +
                        "and sign_time='" + time2 + "' and sign_type='" + signInfoPullDTO.getSign_type() + "'";
                existName = getReportingToOne(sql);
            }
        }
        int result = 0;
        if (existName.equals("0")||existName.equals("")) {
            String QUERY = "";
            QUERY += "insert into sign_info(signId,uerId,sign_time,IP,create_time,sign_type,sign_desc,organization_id) VALUES('" + uuid + "','" + signInfoPullDTO.getUerId() + "'" +
                    ",'" + time2 + "','" + signInfoPullDTO.getIP() + "','" + time + "','" + signInfoPullDTO.getSign_type() + "','" + signInfoPullDTO.getSign_desc() + "','" + signInfoPullDTO.getOrganization_id() + "')";
            result = this.addReportingTo(QUERY);
        }

        // 判断SQl是否执行成功
        if (result != 0) {
            resultDTO.setStatus("90000");
            resultDTO.setDescritpion(warnSrc.add_success);

            if("1".equals(signInfoPullDTO.getSign_type())){
                //添加签到日志
                OperatorLogPullDTO operatorlogPullDTO = new OperatorLogPullDTO();

                operatorlogPullDTO.setOperator_log_type("签到");
                operatorlogPullDTO.setOperator_log_title("用户签到");
                operatorlogPullDTO.setOperator_log_content("OA签到成功");
                operatorlogPullDTO.setMember_id(signInfoPullDTO.getUerId());
                operatorLogResource.addOperatorLogInfo(operatorlogPullDTO);
            }else if("2".equals(signInfoPullDTO.getSign_type())){
                //添加签退日志
                OperatorLogPullDTO operatorlogPullDTO = new OperatorLogPullDTO();

                operatorlogPullDTO.setOperator_log_type("签退");
                operatorlogPullDTO.setOperator_log_title("用户签退");
                operatorlogPullDTO.setOperator_log_content("OA签退成功");
                operatorlogPullDTO.setMember_id(signInfoPullDTO.getUerId());
                operatorLogResource.addOperatorLogInfo(operatorlogPullDTO);
            }
        } else {
            resultDTO.setStatus("90001");//
            resultDTO.setDescritpion("不能重复签到");
        }
        return resultDTO;
    }

    @Override
    public ResultDTO deleteSignInfo(signInfoPullDTO signInfoPullDTO) {
        return null;
    }

    /**
     * 按条件查询签到信息列表
     *
     * @param search
     */
    @Override
    public ResultDTO getSignInfoList(String search) {

        ResultDTO resultDTO = new ResultDTO();

        List<SignInfoListShowDTO> result = new ArrayList<SignInfoListShowDTO>();

        try {
            JSONObject jsonObject = new JSONObject(search);

            String userId = jsonObject.getString("userId");
            String start_time = jsonObject.getString("start_time");
            String end_time = jsonObject.getString("end_time");

            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");

            int day = (int)((sdf.parse(end_time).getTime()-sdf.parse(start_time).getTime())/(1000*3600*24))+1;//计算两个日期之间的天数（包括起止日期）

            Calendar c = Calendar.getInstance();
            c.setTime(sdf.parse(start_time));
            c.add(Calendar.DAY_OF_MONTH, -1);
            for(int i=0;i<day;i++){
                c.add(Calendar.DAY_OF_MONTH, 1);

                SignInfoListShowDTO signInfoListShowDTO = new SignInfoListShowDTO();
                signInfoListShowDTO.setUserId(userId);
                signInfoListShowDTO.setSign_date(sdf.format(c.getTime()));

                List<SignInfoShowDTO> signInfoList = new ArrayList<SignInfoShowDTO>();
                List<SignInfoShowDTO> signOutInfoList = new ArrayList<SignInfoShowDTO>();
                //获取该天的签到信息
                String sql = "SELECT signId,uerId,create_time,IP,sign_type,sign_desc,organization_id FROM sign_info " +
                        "WHERE sign_time = '"+sdf.format(c.getTime())+"' AND uerId = '"+userId+"' ORDER BY create_time";
                List<String[]> signInfo = getReportingTo(sql);

                for(int j=0;j<signInfo.size();j++){
                    SignInfoShowDTO signInfoShowDTO = new SignInfoShowDTO();
                    signInfoShowDTO.setSignId(signInfo.get(j)[1]);
                    signInfoShowDTO.setUerId(signInfo.get(j)[2]);
                    signInfoShowDTO.setTime(signInfo.get(j)[3]);
                    signInfoShowDTO.setIP(signInfo.get(j)[4]);
                    signInfoShowDTO.setSign_type(signInfo.get(j)[5]);
                    signInfoShowDTO.setSign_desc(signInfo.get(j)[6]);
                    signInfoShowDTO.setOrganization_id(signInfo.get(j)[7]);

                    if(signInfo.get(j)[5].equals("1")){
                        signInfoList.add(signInfoShowDTO);
                    }else if(signInfo.get(j)[5].equals("2")){
                        signOutInfoList.add(signInfoShowDTO);
                    }
                }
                signInfoListShowDTO.setSignInfoList(signInfoList);
                signInfoListShowDTO.setSignOutInfoList(signOutInfoList);
                result.add(signInfoListShowDTO);
            }

            if (result.size() != 0) {
                resultDTO.setStatus("90000");
                resultDTO.setDescritpion(warnSrc.get_success);
            } else {
                resultDTO.setStatus("90001");
                resultDTO.setDescritpion(warnSrc.get_fail);
            }
            resultDTO.setStr(result.size()+"");
            resultDTO.setValues(result);
        } catch (JSONException e) {
            e.printStackTrace();
        } catch (ParseException e) {
            e.printStackTrace();
        }
        return resultDTO;
    }

    /**
     * 导出签到信息列表
     *
     * @param search
     */
    @Override
    public ResultDTO exportSignInfo(String search) {

        ResultDTO resultDTO = new ResultDTO();

        String downloadUrl = "";

        String pdfDownLoadUrl = "";

        List<SignInfoListShowDTO> signInfo = getSignInfoList(search).getValues();

        List<SignInfoDTO> result = new ArrayList<SignInfoDTO>();

        List<String[]> seresult = new ArrayList<String[]>();

        for(int i=0;i<signInfo.size();i++){

            SignInfoDTO signInfoDTO = new SignInfoDTO();

            signInfoDTO.setSign_date(signInfo.get(i).getSign_date());

            String sign_IP = "";//签到IP
            String sign_time = "";//签到时间
            String sign_desc = "";//签到备注
            String sign_out_IP = "";//签退IP
            String sign_out_time = "";//签退时间
            String sign_out_desc = "";//签退备注
            if(signInfo.get(i).getSignInfoList().size()>0){
                sign_IP = signInfo.get(i).getSignInfoList().get(signInfo.get(i).getSignInfoList().size()-1).getIP();
                signInfoDTO.setSign_IP(sign_IP);
                sign_time = signInfo.get(i).getSignInfoList().get(signInfo.get(i).getSignInfoList().size()-1).getTime();
                signInfoDTO.setSign_time(sign_time);
                sign_desc = signInfo.get(i).getSignInfoList().get(signInfo.get(i).getSignInfoList().size()-1).getSign_desc();
                signInfoDTO.setSign_desc(sign_desc);
            }
            if(signInfo.get(i).getSignOutInfoList().size()>0){
                sign_out_IP = signInfo.get(i).getSignOutInfoList().get(signInfo.get(i).getSignOutInfoList().size()-1).getIP();
                signInfoDTO.setSign_out_IP(sign_out_IP);
                sign_out_time = signInfo.get(i).getSignOutInfoList().get(signInfo.get(i).getSignOutInfoList().size()-1).getTime();
                signInfoDTO.setSign_out_time(sign_out_time);
                sign_out_desc = signInfo.get(i).getSignOutInfoList().get(signInfo.get(i).getSignOutInfoList().size()-1).getSign_desc();
                signInfoDTO.setSign_out_desc(sign_out_desc);
            }

            result.add(signInfoDTO);
            String[] sign = {"",signInfo.get(i).getSign_date(),sign_IP,sign_time,sign_desc,sign_out_IP,sign_out_time,sign_out_desc};
            seresult.add(sign);
        }

        String[] headers = {"日期", "签到IP", "签到时间", "签到备注", "签退IP", "签退时间", "签退备注"};
        String title = "签到统计";
        String filename = "签到统计表";
        ExportExcel<SignInfoDTO> exportExcel = new ExportExcel<SignInfoDTO>();

        downloadUrl = exportExcel.createExcelFile(exportExcel, title, headers, result, filename);

        try {
            pdfDownLoadUrl = ExportPDF.createPDFFile(seresult, title, headers, filename);
        } catch (DocumentException e) {
            e.printStackTrace();
        }
        if(null != downloadUrl && "" != downloadUrl){
            resultDTO.setStatus("90000");
            resultDTO.setDescritpion(downloadUrl);
            resultDTO.setStr(pdfDownLoadUrl);
            resultDTO.setValues(result);
        }else{
            resultDTO.setStatus("90001");
        }
        return resultDTO;
    }

    /**
     * 导出所有人员签到信息列表
     *
     * @param search
     */
    @Override
    public ResultDTO exportAllSignInfo(String search) {

        ResultDTO resultDTO = new ResultDTO();

        String downloadUrl = "";

        String pdfDownLoadUrl = "";

        List<String[]> pdf_result = new ArrayList<String[]>();

        try {

//            String outFilePath = "";
//            String downLoadPath = "";
//
//            String unloadPath = "";

            JSONObject jsonObject = new JSONObject(search);

            String start_time = jsonObject.getString("start_time");
            String end_time = jsonObject.getString("end_time");

            String sql_w = "";//查询条件sql组合
            if (jsonObject.has("organization_id")) {
                sql_w += " AND m.organization_id = '" + jsonObject.getString("organization_id") + "'";
            }
            if (jsonObject.has("userId")) {
                sql_w += " AND m.member_id = '" + jsonObject.getString("userId") + "'";
            }

//            // 声明一个工作薄
//            HSSFWorkbook workbook = new HSSFWorkbook();
//            // 生成一个表格
//            HSSFSheet sheet = workbook.createSheet("签到统计表");
//            // 设置表格默认列宽度为15个字节
//            sheet.setDefaultColumnWidth(15);
//            // 生成一个样式
//            HSSFCellStyle style = workbook.createCellStyle();
//            // 设置这些样式
//            style.setFillForegroundColor(HSSFColor.SKY_BLUE.index);
//            style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
//            style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
//            style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
//            style.setBorderRight(HSSFCellStyle.BORDER_THIN);
//            style.setBorderTop(HSSFCellStyle.BORDER_THIN);
//            style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
//            // 生成一个字体
//            HSSFFont font = workbook.createFont();
//            font.setColor(HSSFColor.VIOLET.index);
//            font.setFontHeightInPoints((short) 12);
//            font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
//            // 把字体应用到当前的样式
//            style.setFont(font);
//            // 生成并设置另一个样式
//            HSSFCellStyle style2 = workbook.createCellStyle();
//            style2.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.index);
//            style2.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
//            style2.setBorderBottom(HSSFCellStyle.BORDER_THIN);
//            style2.setBorderLeft(HSSFCellStyle.BORDER_THIN);
//            style2.setBorderRight(HSSFCellStyle.BORDER_THIN);
//            style2.setBorderTop(HSSFCellStyle.BORDER_THIN);
//            style2.setAlignment(HSSFCellStyle.ALIGN_CENTER);
//            style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
//            // 生成另一个字体
//            HSSFFont font2 = workbook.createFont();
//            font2.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
//            // 把字体应用到当前的样式
//            style2.setFont(font2);
//            // 声明一个画图的顶级管理器
//            HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
//            // 定义注释的大小和位置,详见文档
//            HSSFComment comment = patriarch.createComment(new HSSFClientAnchor(0,
//                    0, 0, 0, (short) 4, 2, (short) 6, 5));
//            // 设置注释内容
////            comment.setString(new HSSFRichTextString("可以在POI中添加注释！"));
//            // 设置注释作者，当鼠标移动到单元格上是可以在状态栏中看到该内容.
//            comment.setAuthor("leno");
//            // 产生表格标题行
//            HSSFRow row = sheet.createRow(0);
//            HSSFRow row1 = sheet.createRow(1);
//            HSSFCell cell0 = row1.createCell(0);
//            cell0.setCellStyle(style);
//            cell0.setCellValue("部门");
//            HSSFCell cell1 = row1.createCell(1);
//            cell1.setCellStyle(style);
//            cell1.setCellValue("姓名");

            //获取所有人员
            String member = "SELECT m.member_id,m.member_name,m.organization_id,o.organization_name FROM kam_member m " +
                    "JOIN organization o ON m.organization_id = o.organization_id WHERE userId > 2 " + sql_w + " ORDER BY nickname";

            List<String[]> member_result = getReportingTo(member);

            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");

            int day = (int)((sdf.parse(end_time).getTime()-sdf.parse(start_time).getTime())/(1000*3600*24))+1;//计算两个日期之间的天数（包括起止日期）

            final String dayNames[] = { "(星期日)", "(星期一)", "(星期二)", "(星期三)", "(星期四)", "(星期五)","(星期六)" };
            Calendar c = Calendar.getInstance();
            c.setTime(sdf.parse(start_time));
            c.add(Calendar.DAY_OF_MONTH, -1);
            for(int i=0;i<day;i++) {
                c.add(Calendar.DAY_OF_MONTH, 1);

                SignInfoDTO signInfoDTO = new SignInfoDTO();
                signInfoDTO.setSign_date(sdf.format(c.getTime())+dayNames[c.get(c.DAY_OF_WEEK)-1]);

//                HSSFCell cell = row.createCell(i*4+2);
//                cell.setCellStyle(style);
//                sheet.addMergedRegion(new CellRangeAddress(0, 0, i*4+2, i*4+5));//合并单元格
//                cell.setCellValue(sdf.format(c.getTime())+dayNames[c.get(c.DAY_OF_WEEK)-1]);
//                HSSFCell cell2 = row1.createCell(i*4+2);
//                cell2.setCellStyle(style);
//                cell2.setCellValue("签到时间");
//                HSSFCell cell3 = row1.createCell(i*4+3);
//                cell3.setCellStyle(style);
//                cell3.setCellValue("签到备注");
//                HSSFCell cell4 = row1.createCell(i*4+4);
//                cell4.setCellStyle(style);
//                cell4.setCellValue("签退时间");
//                HSSFCell cell5 = row1.createCell(i*4+5);
//                cell5.setCellStyle(style);
//                cell5.setCellValue("签退备注");

                //pdf 内容
                for(int j=0;j<member_result.size();j++){

                    List<String> pdf_text = new ArrayList<String>();
                    pdf_text.add("");
                    pdf_text.add(sdf.format(c.getTime())+dayNames[c.get(c.DAY_OF_WEEK)-1]);
                    pdf_text.add(member_result.get(j)[4]);
                    pdf_text.add(member_result.get(j)[2]);

                    //获取该天该人的签到信息
                    String sign_sql = "SELECT signId,uerId,create_time,IP,sign_type,sign_desc FROM sign_info " +
                            "WHERE sign_type = '1' AND sign_time = '"+sdf.format(c.getTime())+"' AND uerId = '"+member_result.get(j)[1]+"' ORDER BY create_time";

                    List<String[]> sign = getReportingTo(sign_sql);
                    if(sign.size()>0){
                        pdf_text.add(sign.get(0)[3]);
                        pdf_text.add(sign.get(0)[6]);
                    }else {
                        pdf_text.add("未签到");
                        //检查是否请假

                        String check_vacation = "SELECT vt.vacation_type,vf.vacationresion,vf.vacation_form_starttime,vf.vacation_form_endtime FROM vacation_form vf " +
                                "JOIN vacationtype vt ON vf.vacationtypename = vt.vacationtypename " +
                                "WHERE vf.isverify = '1' AND vf.member_id = '"+member_result.get(j)[1]+"' " +
                                "AND vf.vacation_form_starttime <= '"+sdf.format(c.getTime())+" 08:30' AND vf.vacation_form_endtime >= '"+sdf.format(c.getTime())+" 08:30'";

                        List<String[]> vacation_info = getReportingTo(check_vacation);

                        if(vacation_info.size()>0){
                            pdf_text.add("类型："+vacation_info.get(0)[1]+"；原因："+vacation_info.get(0)[2]+"；时间："+vacation_info.get(0)[3]+"至"+vacation_info.get(0)[4]);
                        }else {
                            pdf_text.add("");
                        }
                    }

                    //获取该天的签退信息
                    String sign_out_sql = "SELECT signId,uerId,create_time,IP,sign_type,sign_desc FROM sign_info " +
                            "WHERE sign_type = '2' AND sign_time = '"+sdf.format(c.getTime())+"' AND uerId = '"+member_result.get(j)[1]+"' ORDER BY create_time DESC";

                    List<String[]> sign_out = getReportingTo(sign_out_sql);

                    if(sign_out.size()>0){
                        pdf_text.add(sign_out.get(0)[3]);
                        pdf_text.add(sign_out.get(0)[6]);
                    }else {
                        pdf_text.add("未签退");
                        //检查是否请假

                        String check_vacation = "SELECT vt.vacation_type,vf.vacationresion,vf.vacation_form_starttime,vf.vacation_form_endtime FROM vacation_form vf " +
                                "JOIN vacationtype vt ON vf.vacationtypename = vt.vacationtypename " +
                                "WHERE vf.isverify = '1' AND vf.member_id = '"+member_result.get(j)[1]+"' " +
                                "AND vf.vacation_form_starttime <= '"+sdf.format(c.getTime())+" 16:30' AND vf.vacation_form_endtime >= '"+sdf.format(c.getTime())+" 16:30'";

                        List<String[]> vacation_info = getReportingTo(check_vacation);

                        if(vacation_info.size()>0){
                            pdf_text.add("类型："+vacation_info.get(0)[1]+"；原因："+vacation_info.get(0)[2]+"；时间："+vacation_info.get(0)[3]+"至"+vacation_info.get(0)[4]);
                        }else {
                            pdf_text.add("");
                        }
                    }
                    //转换签到内容
                    String[] sign_info = new String[pdf_text.size()];
                    // List转换成数组
                    for (int k = 0; k < pdf_text.size(); k++) {
                        sign_info[k] = pdf_text.get(k);
                    }
                    pdf_result.add(sign_info);
                }
            }

//            for(int i=0;i<member_result.size();i++){
//                HSSFRow row_member = sheet.createRow(i+2);
//
//                HSSFCell cell00 = row_member.createCell(0);
//                cell00.setCellStyle(style);
//                cell00.setCellValue(member_result.get(i)[4]);
//                HSSFCell cell01 = row_member.createCell(1);
//                cell01.setCellStyle(style2);
//                cell01.setCellValue(member_result.get(i)[2]);

//                Calendar c1 = Calendar.getInstance();
//                c1.setTime(sdf.parse(start_time));
//                c1.add(Calendar.DAY_OF_MONTH, -1);

//                //每人每天的签到记录
//                for(int j=0;j<day;j++) {
//                    c1.add(Calendar.DAY_OF_MONTH, 1);
//
//                    //获取该天的签到信息
//                    String sign_sql = "SELECT signId,uerId,create_time,IP,sign_type,sign_desc FROM sign_info " +
//                            "WHERE sign_type = '1' AND sign_time = '"+sdf.format(c1.getTime())+"' AND uerId = '"+member_result.get(i)[1]+"' ORDER BY create_time";
//
//                    List<String[]> sign = getReportingTo(sign_sql);
//                    if(sign.size()>0){
//                        HSSFCell cell_sign = row_member.createCell(j*4+2);
//                        cell_sign.setCellStyle(style2);
//                        cell_sign.setCellValue(sign.get(0)[3]);
//                        HSSFCell cell_sign_desc = row_member.createCell(j*4+3);
//                        cell_sign_desc.setCellStyle(style2);
//                        cell_sign_desc.setCellValue(sign.get(0)[6]);
//                    }else {
//                        HSSFCell cell_sign = row_member.createCell(j*4+2);
//                        cell_sign.setCellStyle(style2);
//                        cell_sign.setCellValue("未签到");
//                        HSSFCell cell_sign_desc = row_member.createCell(j*4+3);
//                        cell_sign_desc.setCellStyle(style2);
//                        cell_sign_desc.setCellValue("");
//                    }
//
//                    //获取该天的签退信息
//                    String sign_out_sql = "SELECT signId,uerId,create_time,IP,sign_type,sign_desc FROM sign_info " +
//                            "WHERE sign_type = '2' AND sign_time = '"+sdf.format(c1.getTime())+"' AND uerId = '"+member_result.get(i)[1]+"' ORDER BY create_time DESC";
//
//                    List<String[]> sign_out = getReportingTo(sign_out_sql);
//
//                    if(sign_out.size()>0){
//                        HSSFCell cell_sign_out = row_member.createCell(j*4+4);
//                        cell_sign_out.setCellStyle(style2);
//                        cell_sign_out.setCellValue(sign_out.get(0)[3]);
//                        HSSFCell cell_sign_out_desc = row_member.createCell(j*4+5);
//                        cell_sign_out_desc.setCellStyle(style2);
//                        cell_sign_out_desc.setCellValue(sign_out.get(0)[6]);
//                    }else {
//                        HSSFCell cell_sign_out = row_member.createCell(j*4+4);
//                        cell_sign_out.setCellStyle(style2);
//                        cell_sign_out.setCellValue("未签退");
//                        HSSFCell cell_sign_out_desc = row_member.createCell(j*4+5);
//                        cell_sign_out_desc.setCellStyle(style2);
//                        cell_sign_out_desc.setCellValue("");
//                    }
//                }
//            }


//            //获取当前工程目录
//            File f = new File(System.getProperty("user.dir"));
//
//            //获取操作系统类型
//            String os = System.getProperty("os.name");
//
//            //判断操作系统类型
//            if(os.toLowerCase().startsWith("win")){
//                //excel在服务器中所在路径
//                downLoadPath = "/download/excel/";
//                unloadPath = f.getParent().replaceAll("\\\\","/") + "/welcome-content" + downLoadPath;
//            }
//            else{
//                //excel在服务器中所在路径
//                downLoadPath = "/download/excel/";
//                unloadPath = f.getParent() + "/welcome-content" + downLoadPath;
//            }
//
//            File dirFile = new File(unloadPath);
//            //如果dir对应的文件不存在，或者不是一个目录，则退出
//            if (!dirFile.exists() || !dirFile.isDirectory()) {
//                dirFile.mkdirs();
//            }
//
//            outFilePath = unloadPath + start_time + "至" + end_time + "签到统计表" + ".xls";
//
//            //输出文档路径及名称
//            File outFile = new File(outFilePath);
//            if (!outFile.exists()) {
//                outFile.createNewFile();
//            }
//
//            //构造输出流输出文档
//            OutputStream out = new FileOutputStream(outFile);
//            workbook.write(out);
//            out.close();
//            System.out.println("excel导出成功！");
//
//            downloadUrl = downLoadPath + start_time + "至" + end_time + "签到统计表" + ".xls";

            String[] headers = {"日期", "部门", "姓名", "签到时间", "签到备注", "签退时间", "签退备注"};
            String title;
            String filename;
            if(start_time.equals(end_time)){
                title = start_time + "签到统计";
                filename = start_time + "签到统计表";
            }else {
                title = start_time + "至" + end_time + "签到统计";
                filename = start_time + "至" + end_time + "签到统计表";
            }
            pdfDownLoadUrl = ExportPDF.createPDFFile(pdf_result, title, headers, filename);

            List<AllSignInfoDTO> result = new ArrayList<AllSignInfoDTO>();
            if(pdf_result.size()>0){
                for(int i=0;i<pdf_result.size();i++){
                    AllSignInfoDTO allSignInfoDTO = new AllSignInfoDTO();

                    allSignInfoDTO.setSign_date(pdf_result.get(i)[1]);
                    allSignInfoDTO.setOrganization_name(pdf_result.get(i)[2]);
                    allSignInfoDTO.setUser_name(pdf_result.get(i)[3]);
                    allSignInfoDTO.setSign_time(pdf_result.get(i)[4]);
                    allSignInfoDTO.setSign_desc(pdf_result.get(i)[5]);
                    allSignInfoDTO.setSign_out_time(pdf_result.get(i)[6]);
                    allSignInfoDTO.setSign_out_desc(pdf_result.get(i)[7]);

                    result.add(allSignInfoDTO);
                }
            }
            if(result.size()!=0){
                resultDTO.setValues(result);
            }
            ExportExcel<AllSignInfoDTO> exportExcel = new ExportExcel<AllSignInfoDTO>();

            downloadUrl = exportExcel.createExcelFile(exportExcel, title, headers, result, filename);
        } catch (JSONException e) {
            e.printStackTrace();
        } catch (ParseException e) {
            e.printStackTrace();
        } catch (DocumentException e) {
            e.printStackTrace();
        }

        if(null != downloadUrl && "" != downloadUrl){
            resultDTO.setStatus("90000");
            resultDTO.setDescritpion(downloadUrl);
            resultDTO.setStr(pdfDownLoadUrl);
        }else{
            resultDTO.setStatus("90001");
        }
        return resultDTO;
    }
}
